# -*- coding: utf-8 -*-
#coding = utf-8

'''
Created on June 3, 2013

@author: Tong Zhang

Mysql DataBase Manager include: the connection pool in the DBUtils PooledDB , the batching insert operation in the MySQLdb
'''

import time

import MySQLdb
from DBUtils.PooledDB import PooledDB
from config import DBConfig

class DBManager:
    def __init__(self):
        self.host = DBConfig.MysqlHost
        self.user = DBConfig.MysqlUser
        self.passwd = DBConfig.MysqlPwd
        self.db = DBConfig.MysqlDBName
        self.conn = None

    @staticmethod
    def escape_string(content):
        content = MySQLdb.escape_string(content)
        return content

    def connect(self):
        #logger = logging.getLogger('et.kernel.DM.DBManager.connect')
        if self.conn:
            return

        while True:
            try:
                self.conn = PooledDB(MySQLdb, maxusage=10, db=self.db, host=self.host, user=self.user, passwd=self.passwd, charset='utf8')
                break
            except Exception, e:
                #logger.error(e)
                #logger.error('Sleep %d seconds for next try.' % DBConfig.SleepSecond)
                time.sleep(DBConfig.SleepSecond)

    def disconnect(self):
        #logger = logging.getLogger('et.kernel.DM.DBManager.disconnect')
        if not self.conn:
            return
        try:
            self.conn.close()
            self.conn = None

        except Exception, e:
            #logger.error(e)
            print e

    def operateDB(self, type, *sqls):

        if not self.conn:
            self.connect()
        
        pool = self.conn.connection()
        cur = pool.cursor(cursorclass=MySQLdb.cursors.DictCursor)
        result = []
        if type == 'get':
            if cur.execute(sqls[0]) > 0:
                result = cur.fetchall()
                cur.close()
                pool.close()
                return result
            else:
                return []
        elif type == 'set':
            for sql in sqls:
                cur.execute(sql)
                result = cur.fetchall()
                cur.close()
                pool.commit()
                pool.close()
                return result
        else:
            for sql in sqls:
                cur.execute(sql)
                cur.close()
                pool.commit()
                pool.close()
                return True

    def batch_insert(self,sql,tmp):

        result = 0
        if not self.conn:
            self.connect()
        try:
            pool = self.conn.connection()
            cur = pool.cursor()
            result = cur.executemany(sql, tmp)
            cur.close()
            pool.commit()
            pool.close()
            return result

        except Exception, e:
            #logger.error(e)
            return 0 

    def get_results(self,sql,cnt):

        if not self.conn:
            self.connect()

        pool = self.conn.connection()
        cur = pool.cursor()#cursorclass=MySQLdb.cursors.DictCursor)
        result = []
        cur.execute(sql)
        count = 0
        while count < cnt:

            result.append(cur.fetchall())
            cur.nextset()
            count = count + 1

        cur.close()
        pool.commit()
        pool.close()
        return result

    def get_results_dic(self,sql,cnt):

        if not self.conn:
            self.connect()

        pool = self.conn.connection()
        cur = pool.cursor(cursorclass=MySQLdb.cursors.DictCursor)
        result = []
        cur.execute(sql)
        count = 0
        while count < cnt:

            result.append(cur.fetchall())
            cur.nextset()
            count = count + 1

        cur.close()
        pool.commit()
        pool.close()
        return result

            
# demo batchinsert            
'''
test = DataBaseManager('localhost', 'root', 'zhangtong', 'test')
i = 20010
l = []
while i < 20015:
    l.append(str(i))
    i = i + 1

t = tuple(l)
sql = "INSERT INTO testsportdata VALUES(null,%s,NOW(),NOW())"
test.many_insert(sql,t)
'''